--    Program: 		CAPPOIMP_APPROVAL_GROUP_ASSIGNMENT.sql
--    Description:  Import PO APPROVAL GROUP ASSIGNMENT information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_control_group_id		number;
   l_job_id					number;
   l_position_id            number;
                    		                 		
   cursor cur_po_position_controls_all is
      select * from CRST_PO_POSITION_CONTROLS_ALL;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_po_position_controls_all loop   	
     l_err_msg_tmp := '';
     
     l_control_group_id := null; 	
     begin
       SELECT control_group_id_n
       INTO   l_control_group_id
       FROM   CRST_PO_CONTROL_GROUPS_ALL
       WHERE  control_group_id = cur.CONTROL_GROUP_ID;
     exception
       when others then
         l_err_msg_tmp := 'New Control Group ID '||cur.CONTROL_GROUP_ID||' not found';
     end;
     
     l_position_id := null; 	
     begin
       SELECT position_id_n
       INTO   l_position_id
       FROM   CRST_PER_ALL_POSITIONS
       WHERE  position_id = cur.POSITION_ID;
     exception
       when others then
         l_err_msg_tmp := 'New Position ID '||cur.POSITION_ID||' not found';
     end;
     
     l_job_id := null; 	
     begin
       SELECT job_id_n
       INTO   l_job_id
       FROM   CRST_PER_JOBS
       WHERE  job_id = cur.JOB_ID;
     exception
       when others then
         l_err_msg_tmp := 'New Job ID '||cur.JOB_ID||' not found';
     end;  	  
     
     begin
       -- import data
       insert into PO_POSITION_CONTROLS_ALL
       (
              POSITION_ID             ,
              CONTROL_GROUP_ID        ,
              LAST_UPDATE_DATE        ,
              LAST_UPDATED_BY         ,
              LAST_UPDATE_LOGIN       ,
              CREATION_DATE           ,
              CREATED_BY              ,
              JOB_ID                  ,
              ORGANIZATION_ID         ,
              START_DATE              ,
              END_DATE                ,
              CONTROL_FUNCTION_ID     ,
              PROGRAM_APPLICATION_ID  ,
              PROGRAM_ID              ,
              PROGRAM_UPDATE_DATE     ,
              REQUEST_ID              ,
              ATTRIBUTE1              ,
              ATTRIBUTE10             ,
              ATTRIBUTE11             ,
              ATTRIBUTE12             ,
              ATTRIBUTE13             ,
              ATTRIBUTE14             ,
              ATTRIBUTE15             ,
              ATTRIBUTE2              ,
              ATTRIBUTE3              ,
              ATTRIBUTE4              ,
              ATTRIBUTE5              ,
              ATTRIBUTE6              ,
              ATTRIBUTE7              ,
              ATTRIBUTE8              ,
              ATTRIBUTE9              ,
              ATTRIBUTE_CATEGORY      ,
              ORG_ID                   
       )
       values
       (
              l_position_id               ,
              l_control_group_id          ,
              cur.LAST_UPDATE_DATE        ,
              CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
              cur.LAST_UPDATE_LOGIN       ,
              cur.CREATION_DATE           ,
              CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
              l_job_id                    ,
              CAPBRIMP_MAPPING.GetOrganizationMapping(cur.ORGANIZATION_ID),
              cur.START_DATE              ,
              cur.END_DATE                ,
              cur.CONTROL_FUNCTION_ID     ,
              cur.PROGRAM_APPLICATION_ID  ,
              cur.PROGRAM_ID              ,
              cur.PROGRAM_UPDATE_DATE     ,
              cur.REQUEST_ID              ,
              cur.ATTRIBUTE1              ,
              cur.ATTRIBUTE10             ,
              cur.ATTRIBUTE11             ,
              cur.ATTRIBUTE12             ,
              cur.ATTRIBUTE13             ,
              cur.ATTRIBUTE14             ,
              cur.ATTRIBUTE15             ,
              cur.ATTRIBUTE2              ,
              cur.ATTRIBUTE3              ,
              cur.ATTRIBUTE4              ,
              cur.ATTRIBUTE5              ,
              cur.ATTRIBUTE6              ,
              cur.ATTRIBUTE7              ,
              cur.ATTRIBUTE8              ,
              cur.ATTRIBUTE9              ,
              cur.ATTRIBUTE_CATEGORY      ,
              CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID)
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_APPROVAL_GROUP_ASSIGNMENT', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
